package com.fenrirtec.aepp.common.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fenrirtec.aepp.common.dao.AttachmentDao;
import com.fenrirtec.aepp.common.model.Attachment;
import com.fenrirtec.aepp.common.model.Upload;
import com.fenrirtec.core.database.DatabaseSessionManager;
import com.fenrirtec.core.database.ResultSetMapper;
import com.fenrirtec.core.exception.DatabaseException;
import com.fenrirtec.core.utils.DatabaseUtils;

public class AttachmentDao_JDBCImpl implements AttachmentDao {
	
	private static final Logger logger = LoggerFactory.getLogger(AttachmentDao_JDBCImpl.class);

	@Override
	public Integer saveAttachment(Integer fileType, String path, String comment, String uploadUser) {
		if (logger.isInfoEnabled()) {
			logger.info("[AttachmentDao_JDBCImpl#saveAttachment] start.");
		}

		PreparedStatement statement = null;
		
		try {
			
			StringBuilder sql = new StringBuilder()
				.append("insert into t_attachment ( \n")
				.append("  file_type, \n")
				.append("  path, \n")
				.append("  comment, \n")
				.append("  upload_user, \n")
				.append("  create_user) \n")
				.append("values ( \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?) \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[AttachmentDao_JDBCImpl#saveAttachment] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setInt(1, fileType);
			statement.setString(2, path);
			statement.setString(3, comment);
			statement.setString(4, uploadUser);
			statement.setString(5, uploadUser);
			statement.executeUpdate();
			
			return DatabaseUtils.getLastInsertId();
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[AttachmentDao_JDBCImpl#saveAttachment] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[AttachmentDao_JDBCImpl#saveAttachment] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

	@Override
	public Attachment searchAttachment(Integer attachmentId) {
		if (logger.isInfoEnabled()) {
			logger.info("[AttachmentDao_JDBCImpl#searchAttachment] start.");
		}

		PreparedStatement statement = null;
		
		try {
			
			StringBuilder sql = new StringBuilder()
				.append("select \n")
				.append("  attachment_id, \n")
				.append("  file_type, \n")
                .append("  file_category, \n")
				.append("  path, \n")
				.append("  comment, \n")
				.append("  upload_user, \n")
				.append("  upload_date, \n")
				.append("  create_user, \n")
				.append("  create_date, \n")
				.append("  update_user, \n")
				.append("  update_date, \n")
				.append("  delete_flag \n")
				.append("from \n")
				.append("  t_attachment \n")
				.append("where \n")
				.append("  attachment_id = ? \n")
				.append("and \n")
				.append("  delete_flag = false \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[AttachmentDao_JDBCImpl#searchAttachment] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setLong(1, attachmentId);
			ResultSet resultSet = statement.executeQuery();
			
			ResultSetMapper<Attachment> resultSetMapper = new ResultSetMapper<Attachment>();
			Attachment attachment = resultSetMapper.mapResultSetToObject(resultSet, Attachment.class);	
			
			return attachment;
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[AttachmentDao_JDBCImpl#searchAttachment] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[AttachmentDao_JDBCImpl#searchAttachment] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

	@Override
	public void relateMemberMaterial(String memberLoginName, String uploadFileCategory, Integer attachmentId) {
		if (logger.isInfoEnabled()) {
			logger.info("[AttachmentDao_JDBCImpl#relateMemberMaterial] start.");
		}

		PreparedStatement statement = null;
		
		try {
			
			String columnName = "";
			
			switch (uploadFileCategory) {
				case "upload_contact_front":
					columnName = "image_contact_certificates_front";
					break;
				case "upload_contact_back":
					columnName = "image_contact_certificates_back";
					break;
				case "upload_license":
					columnName = "image_license";
					break;
				case "upload_legal_front":
					columnName = "image_legal_certificates_front";
					break;
				case "upload_legal_back":
					columnName = "image_legal_certificates_back";
					break;
			}
			
			StringBuilder sql = new StringBuilder()
				.append("update \n")
				.append("  t_member \n")
				.append("set \n")
				.append("  " + columnName + " = ? \n")
				.append("where \n")
				.append("  member_login_name = ? \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[AttachmentDao_JDBCImpl#relateMemberMaterial] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setLong(1, attachmentId);
			statement.setString(2, memberLoginName);
			statement.executeUpdate();
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[AttachmentDao_JDBCImpl#relateMemberMaterial] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[AttachmentDao_JDBCImpl#relateMemberMaterial] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

	@Override
	public void removeAttachment(Integer attachmentId) {
		if (logger.isInfoEnabled()) {
			logger.info("[AttachmentDao_JDBCImpl#removeAttachment] start.");
		}

		PreparedStatement statement = null;
		
		try {
			
			StringBuilder sql = new StringBuilder()
				.append("delete from \n")
				.append("  t_attachment \n")
				.append("where \n")
				.append("  attachment_id = ? \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[AttachmentDao_JDBCImpl#removeAttachment] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setLong(1, attachmentId);
			statement.executeUpdate();
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[AttachmentDao_JDBCImpl#removeAttachment] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[AttachmentDao_JDBCImpl#removeAttachment] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

	@Override
	public Integer upload(String person, Integer personType, String purpose, String path, Integer fileType) {
		if (logger.isInfoEnabled()) {
			logger.info("[AttachmentDao_JDBCImpl#upload] start.");
		}

		PreparedStatement statement = null;
		Integer uploadId = null;
		
		try {
			
			StringBuilder sql = new StringBuilder()
				.append("insert into t_upload ( \n")
				.append("  upload_person, \n")
				.append("  upload_person_type, \n")
				.append("  upload_purpose, \n")
				.append("  upload_file_path, \n")
				.append("  upload_file_type) \n")
				.append("values ( \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?) \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[AttachmentDao_JDBCImpl#upload] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, person);
			statement.setInt(2, personType);
			statement.setString(3, purpose);
			statement.setString(4, path);
			statement.setInt(5, fileType);
			statement.executeUpdate();
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[AttachmentDao_JDBCImpl#upload] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[AttachmentDao_JDBCImpl#upload] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
		
		uploadId = DatabaseUtils.getLastInsertId();
		return uploadId;
	}

	@Override
	public Upload uploadInfo(Integer uploadId) {
		if (logger.isInfoEnabled()) {
			logger.info("[AttachmentDao_JDBCImpl#uploadInfo] start.");
		}

		PreparedStatement statement = null;
		
		try {
			
			StringBuilder sql = new StringBuilder()
				.append("select \n")
				.append("  upload_id, \n")
				.append("  upload_person, \n")
				.append("  upload_person_type, \n")
				.append("  upload_purpose, \n")
				.append("  upload_datetime, \n")
				.append("  upload_file_path, \n")
				.append("  upload_file_type \n")
				.append("from \n")
				.append("  t_upload \n")
				.append("where \n")
				.append("  upload_id = ? \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[AttachmentDao_JDBCImpl#uploadInfo] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setInt(1, uploadId);
			ResultSet resultSet = statement.executeQuery();
			
			ResultSetMapper<Upload> resultSetMapper = new ResultSetMapper<Upload>();
			Upload upload = resultSetMapper.mapResultSetToObject(resultSet, Upload.class);	
			
			return upload;
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[AttachmentDao_JDBCImpl#uploadInfo] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[AttachmentDao_JDBCImpl#uploadInfo] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

	@Override
	public void removeUpload(Integer uploadId) {
		if (logger.isInfoEnabled()) {
			logger.info("[AttachmentDao_JDBCImpl#removeUpload] start.");
		}

		PreparedStatement statement = null;
		
		try {
			
			StringBuilder sql = new StringBuilder()
				.append("delete from \n")
				.append("  t_upload \n")
				.append("where \n")
				.append("  upload_id = ? \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[AttachmentDao_JDBCImpl#removeUpload] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setInt(1, uploadId);
			statement.executeUpdate();
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[AttachmentDao_JDBCImpl#removeUpload] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[AttachmentDao_JDBCImpl#removeUpload] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}
}
